--[[
    sqlite数据库表的增删改查
--]]
-------------------模块区-------------------
local logger = require("dx_logger")
local sqlite3 = require("dx_sqlite3")

-------------------变量区-------------------

-------------------函数区-------------------
local function init()
    local client = sqlite3.open("my.db")

    if not client then
        logger.error("[初始化sqlite实例] 失败")
        return nil
    else
        logger.info("[初始化sqlite实例] 完成")
    end
    sqlite3.execute(client,
        [[
      create table if not exists device(
        uuid varchar(128) PRIMARY KEY,
        model varchar(128)
      )
    ]]
    )

    logger.info("[初始化数据库表] 完成")
    return client
end

local function main()
    local client = init()
    if not client then
        return
    end
    -- 清空
    sqlite3.execute(client, "delete from device")

    -- 增
    sqlite3.execute(client, "insert into device ('uuid', 'model') values('001001','cc101')")
    sqlite3.execute(client, "insert into device ('uuid', 'model') values('001002','bg100')")
    sqlite3.execute(client, "insert into device ('uuid', 'model') values('001003','il60')")
    sqlite3.execute(client, "insert into device ('uuid', 'model') values('001004','m300')")

    -- 删
    sqlite3.execute(client, "delete from device where uuid = '001003'")
    sqlite3.execute(client, "delete from device where uuid = '001004'")

    -- 改
    sqlite3.execute(client, "update device set uuid='001003' where uuid = '001001'")
    sqlite3.execute(client, "update device set uuid='001004' where uuid = '001002'")

    -- 查
    local result = sqlite3.query(client, "select * from device")
    for i, v in ipairs(result) do
        logger.info("+-------+-------+")
        local table_name = "|"
        local table_value = "|"
        for k, v1 in pairs(v) do
            table_name = table_name .. k .. "\t" .. "|"
            table_value = table_value .. v1 .. "\t" .. "|"
        end
        if i == 1 then
            logger.info(table_name)
            logger.info("+-------+-------+")
        end
        logger.info(table_value)

        if i == #result then
            logger.info("+-------+-------+")
        end
    end

    -- 销毁数据库
    sqlite3.close(client)
end

-------------------主流程-------------------
main()
